MYSQL - MULTIPLE SET VALUES IN ONE UPDATE STATEMENT USING 2 TABLES AS REFERENCE AND STORING VALUES IN ONE OF THOSE TABLES WITH A SPECIFIC LOGIC.

Hello people,

A problem came up by making an UPDATE. The example issue is as follows:

CURRENUSRTABLE
+------------+-------+
| ID         | STATE |
+------------+-------+
| 123        |     3 |
| 456        |     3 |
| 789        |     3 |
+------------+-------+

HISTORYTABLE
+------------+------------+-----+
| ID         | TRDATE     | ACT |
+------------+------------+-----+
| 123        | 2013-11-01 |   5 |
| 456        | 2013-11-01 |   5 |
| 789        | 2013-11-01 |   5 |
| 123        | 2013-11-02 |   4 |
| 456        | 2013-11-02 |   4 |
| 789        | 2013-11-02 |   4 |
| 123        | 2013-11-03 |   3 |
| 456        | 2013-11-03 |   3 |
| 789        | 2013-11-03 |   3 |
+------------+------------+-----+

I'm using these variables:

@BA=3, @DE=5, @BL=4,

What I'm trying to do is an update on CURRENUSRTABLE.STATE using HISTORYTABLE.ACT with the following logic:

1. STATE value will be updated as ACT value, except when STATE value is 4 and ACT is 3, then STATE will be 5

I made this statement:

UPDATE CURRENUSRTABLE RIGHT OUTER JOIN HISTORYTABLE 
    ON HISTORYTABLE.ID=CURRENUSRTABLE.ID
    SET CURRENUSRTABLE.STATE=
        (
        SELECT CASE HISTORYTABLE.ACT
        WHEN @DE THEN @DE
        WHEN @BL THEN @BL
        WHEN @BA THEN CASE CURRENUSRTABLE.STATE
                WHEN @BL THEN @DE
                ELSE @BA
                END
        END
        ORDER BY
        HISTORYTABLE.TRDATE,FIELD(HISTORYTABLE.ACT,@DE,@BL,@BA)
        )
WHERE
    HISTORYTABLE.TRDATE BETWEEN '2013-11-01' AND '2013-11-01'

I'm intentionally using "RIGHT OUTER JOIN" and "HISTORYTABLE.TRDATE BETWEEN" because I'd like to change the values in CURRENUSRTABLE using a timeframe of more than one day.

If I execute this statement many times using only one day (i.e. "BETWEEN '2013-11-01' AND '2013-11-01'" and then "BETWEEN '2013-11-02' AND '2013-11-02'"... etc ) it works perfectly, but if it is executed using the dates "BETWEEN '2013-11-01' AND '2013-11-03'" the results on CURRENUSRTABLE.STATE are 3, which is wrong, it should be 5.

I think the problem relies on "CASE CURRENUSRTABLE.STATE" when uses "HISTORYTABLE.TRDATE BETWEEN '2013-11-01' AND '2013-11-03'", because it reads the STATE 9 times which has not been commited yet until the statement ends.

Query OK, 9 rows affected (0.00 sec)
Rows matched: 9  Changed: 9  Warnings: 0

Maybe the solution is very simple, but unfortunately I've not much practice on MySQL since I've worked with it less than 2 months :)

Is there any suggestions to solve this issue?

PD: MySQL version is 4.1.22, I know is very old an EOL, unfortunately I have to make these statements on this version.

Thanks!